﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.App;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System.Data;

namespace KD_DMG_ZLFXBILL
{
    [Description("应收帐龄分析报表服务插件")]
    public class YSZLBillReport : SysReportBaseService
    {
        #region 临时表定义
        //临时表集合
        private List<string> tempTable = new List<string>();
        //接存储过程的临时表
        private string T_Temp_First = string.Empty;
        //最终数据表
        private string T_Temp_Last = string.Empty;
        //自己创建的临时表的SQL语句 List
        List<SqlObject> listTableSqlObj = new List<SqlObject>();
        #endregion

        #region 报表加载初始化
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("应收帐龄分析报表过滤条件查询", this.Context.UserLocale.LCID);
            this.ReportProperty.SimpleAllCols = true;
            this.ReportProperty.IsGroupSummary = true; // 支持分组汇总

        }
        #endregion

        /// <summary>
        /// 报表表头数据填充,在列表中用来显示给用户当前查询条件是什么
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override Kingdee.BOS.Core.Report.ReportTitles GetReportTitles(Kingdee.BOS.Core.Report.IRptParams filter)
        {
            String FCheckBox;
            String FSYear, FEYear, FSPeriod, FEPeriod;
            ReportTitles titles = new ReportTitles();
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;




            FCheckBox = Convert.ToString(dyFilter["F_SUN_CheckBox"]);

            FSYear = Convert.ToString(dyFilter["F_SUN_SYear"]);

            FEYear = Convert.ToString(dyFilter["F_SUN_EYear"]);

            FSPeriod = Convert.ToString(dyFilter["F_SUN_SPeriod"]);

            FEPeriod = Convert.ToString(dyFilter["F_SUN_EPeriod"]);

            titles.AddTitle("F_SUN_SYear", FSYear);
            titles.AddTitle("F_SUN_EYear", FEYear);
            titles.AddTitle("F_SUN_SPeriod", FSPeriod);
            titles.AddTitle("F_SUN_EPeriod", FEPeriod);
            titles.AddTitle("F_SUN_CheckBox", FCheckBox);
           // titles.AddTitle("F_MY_ExChange", F_MY_ExChange);


            return titles;
        }
        public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
        {
            ReportHeader header = new ReportHeader();

            header.AddChild("id", new LocaleValue("序号", this.Context.UserLocale.LCID), SqlStorageType.SqlInt).Width =60;
            header.AddChild("FACCOUNTBOOKName", new LocaleValue("公司主体", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCustNumber", new LocaleValue("客户编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCustName", new LocaleValue("客户名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FYWNumber", new LocaleValue("业务类别编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FYWName", new LocaleValue("业务类别名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FXMNumber", new LocaleValue("项目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FXMName", new LocaleValue("项目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAMOUNT", new LocaleValue("应收账款账面余额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FONE", new LocaleValue("1-6个月", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FTWO", new LocaleValue("7-12个月", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FTHREE", new LocaleValue("1-2年", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FFOURE", new LocaleValue("2-3年", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FFIVE", new LocaleValue("3年以上", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FHZAmount", new LocaleValue("坏账准备计提金额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FJEAmount", new LocaleValue("应收账款净额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);

            return header;
        }
        public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
        {
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            //创建所需临时表
            CreatBillDataFirstTempTable();
            T_Temp_Last = tableName;  //获得最终返回数据临时表名
            //执行建表语句
            DBUtils.ExecuteBatch(this.Context, listTableSqlObj);
            listTableSqlObj.Clear();
            //把存储过程里的数据插入临时表里
            InsertFirstTempTable(this.Context, filter);

            //最后再把数导入到最后的返回表里
            string sSQL = @"/*dialect*/ select id, FACCOUNTBOOKName , FCustNumber ,FCustName ,FYWNumber,FYWName ,FXMNumber ,FXMName , " +
                         "FAMOUNT ,FONE ,FTWO ,FTHREE ,FFOURE ,FFIVE ,FHZAmount ,FJEAmount , {0}  into {1} from " + this.T_Temp_First + " a ";

            KSQL_SEQ = string.Format(KSQL_SEQ, "a.ID");
            sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);

            //sSQL =sSQL + sbFilter.ToString();
            DBUtils.Execute(this.Context, sSQL);
            this.DeleteTempTable();   //删除临时表
        }

        private void CreatBillDataFirstTempTable()
        {
            StringBuilder sbSql = new StringBuilder();
            IDBService service = ServiceHelper.GetService<IDBService>();
            T_Temp_First = service.CreateTemporaryTableName(this.Context);
            sbSql.AppendFormat(@"CREATE TABLE {0}
                               (
                                id int ,
                                FACCOUNTBOOKName varchar(100),---公司ID
                                FAMOUNT decimal (19,2),--金额
                                FCustNumber varchar(50),--客户编码
                                FCustName varchar(50),--客户名称
                                FYWNumber varchar(50),--业务类别
                                FYWName varchar(50),--
                                FXMNumber varchar(50),--项目号
                                FXMName varchar(50),
                                FONE decimal(19,2),
                                FTWO decimal(19,2),
                                FTHREE decimal(19,2),
                                FFOURE decimal(19,2),
                                FFIVE decimal(19,2),
                                FHZAmount decimal(19,2),
                                FJEAmount decimal(19,2)
                                ) ", T_Temp_First);
            listTableSqlObj.Add(new SqlObject(sbSql.ToString(), new List<SqlParam>()));
            //记录自己创建的临时表
            tempTable.Add(T_Temp_First);
            sbSql.Clear();
        }

        /// <summary>
        /// 根据过滤条件获得执行存储过程的参数
        /// </summary>
        private string GetSpParams(IRptParams filter)
        {

            string FSYear, FEYear, FSPeriod, FEPeriod, FOrgID, FPOSTID, FCheckBox;
            FPOSTID = "";
            FOrgID = "";
            
   
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;

            FOrgID = this.GetBaseDataByKey(dyFilter, "F_FIND_ORGID", "F_FIND_ORGID_ID", true);


            FCheckBox = Convert.ToString(dyFilter["F_SUN_CheckBox"]);

            if (FCheckBox == "True")
            {
                FPOSTID = "(0,1)";
            }
            else if (FCheckBox == "False")
            {
                FPOSTID = "(1)";
            }

            FSYear = Convert.ToString(dyFilter["F_SUN_SYear"]);

            FEYear = Convert.ToString(dyFilter["F_SUN_EYear"]);

            FSPeriod = Convert.ToString(dyFilter["F_SUN_SPeriod"]);

            FEPeriod = Convert.ToString(dyFilter["F_SUN_EPeriod"]);



            return "'" + FSYear + "','" + FSPeriod + "','" + FEYear + "','" + FEPeriod + "','"+ FPOSTID +"','" + FOrgID + "'";
        }

        /// <summary>
        /// 填充数据到最终的临时表里
        /// </summary>
        private void InsertFirstTempTable(Context ctx, IRptParams filter)
        {
            StringBuilder sql = new StringBuilder();

            //这个时候还没有返回表呢,先创建一下吧 

            sql.Append("/*dialect*/ INSERT INTO " + T_Temp_First);
            sql.Append("  exec SP_YSZLBill_SJ " + GetSpParams(filter));

            DBUtils.Execute(ctx, sql.ToString());
            sql.Clear();
        }

        /// <summary>
        /// 删除临时表操作
        /// </summary>
        public void DeleteTempTable()
        {
            IDBService service = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
            if (this.tempTable.Count > 0)
            {
                //删除临时表
                service.DeleteTemporaryTableName(this.Context, this.tempTable.ToArray());
                this.tempTable.Clear();
            }
        }

        private string GetBaseDataByKey(DynamicObject doFilter, string sKey, string sItem, Boolean isDataList)
        {

            string sRentunValue = string.Empty;
            if (doFilter != null && doFilter[sKey] != null)
            {
                //如果是多选资料
                if (isDataList)
                {

                    DynamicObjectCollection doTempList = doFilter[sKey] as DynamicObjectCollection;
                    for (int i = 0; i < doTempList.Count(); i++)
                    {
                        if (doTempList[i] != null)
                        {
                            DynamicObject doTemp = doTempList[i] as DynamicObject;
                            sRentunValue += "," + Convert.ToString(doTemp[sItem]);

                        }

                    }

                    sRentunValue = sRentunValue.Substring(1, sRentunValue.Length - 1);
                }
                else
                {
                    DynamicObject dotemp = doFilter[sKey] as DynamicObject;
                    sRentunValue = Convert.ToString(dotemp[sItem]);

                }

            }

            return sRentunValue;
        }

    }
}
